/**
 * $version:  0.1 
 * $Date: 2011-01-21 
 *
 * Copyright (C) 2010-2011 Jawa Software. All rights reserved.
 *
 */

package org.jawa.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.jawa.database.DbConnectionManager;
import org.jawa.util.JawaConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 用户操作的默认数据库实现.
 * 
 * @author Matt Tucker
 */
public class DefaultUserProvider implements UserProvider {

	private static final Logger Log = LoggerFactory
			.getLogger(DefaultUserProvider.class);

	private static final String LOAD_USER_BY_USERNAME = "SELECT id, username,name, password, email FROM jawa_user WHERE username=?";
	private static final String LOAD_USER_BY_ID = "SELECT id, username,name, password, email FROM jawa_user WHERE id=? ";

	private static final String USER_COUNT = "SELECT count(*) FROM jawa_user";
	private static final String ALL_USERS = "SELECT id,username,name,password, email FROM jawa_user ORDER BY username";
	private static final String INSERT_USER = "INSERT INTO jawa_user (id,username,name,password,email) "
			+ " VALUES (?,?,?,?,?)";
	private static final String DELETE_USER = "DELETE FROM jawa_user WHERE id=?";

	public User loadUserByUserid(Long userId) throws UserNotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_USER_BY_ID);
			pstmt.setLong(1, userId);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new UserNotFoundException();
			}
			Long id = rs.getLong("id");
			String username = rs.getString("username");
			String password = rs.getString("password");
			String name = rs.getString("name");
			String email = rs.getString("email");

			User user = new User();
			user.setId(id);
			user.setUsername(username);
			user.setPassword(password);
			user.setName(name);
			user.setEmail(email);
			return user;
		} catch (Exception e) {
			throw new UserNotFoundException(e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	public User loadUserByUsername(String username) throws UserNotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_USER_BY_USERNAME);
			pstmt.setString(1, username);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new UserNotFoundException();
			}
			Long id = rs.getLong("id");
			String password = rs.getString("password");
			String name = rs.getString("name");
			String email = rs.getString("email");

			User user = new User();
			user.setId(id);
			user.setUsername(username);
			user.setPassword(password);
			user.setName(name);
			user.setEmail(email);
			return user;
		} catch (Exception e) {
			throw new UserNotFoundException(e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	public User createUser(User user) throws UserAlreadyExistsException {
		try {
			loadUserByUsername(user.getUsername());
			// 登录名称已存在,所以:
			throw new UserAlreadyExistsException("Username "
					+ user.getUsername() + " already exists");
		} catch (UserNotFoundException unfe) {
			String encryptedPassword = null;
			encryptedPassword = DigestUtils.md5Hex(user.getPassword());
			Connection con = null;
			PreparedStatement pstmt = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(INSERT_USER);
				pstmt.setLong(1, user.getId());
				pstmt.setString(2, user.getUsername());
				if (StringUtils.isEmpty(user.getName())) {
					pstmt.setNull(3, Types.VARCHAR);
				} else {
					pstmt.setString(3, user.getName());
				}
				if (encryptedPassword == null) {
					pstmt.setNull(4, Types.VARCHAR);
				} else {
					pstmt.setString(4, encryptedPassword);
				}
				if (StringUtils.isEmpty(user.getEmail())) {
					pstmt.setNull(5, Types.VARCHAR);
				} else {
					pstmt.setString(5, user.getEmail());
				}
				pstmt.execute();
			} catch (Exception e) {
				Log.error("创建用户出错！", e);
			} finally {
				DbConnectionManager.closeConnection(pstmt, con);
			}
			return user;
		}
	}

	public void deleteUser(Long userId) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(DELETE_USER);
			pstmt.setLong(1, userId);
			pstmt.execute();
		} catch (Exception e) {
			Log.error("删除用户出错!", e);
		} finally {
			DbConnectionManager.closeStatement(pstmt);
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	public int getUserCount() {
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(USER_COUNT);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			Log.error("获取用户总数出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return count;
	}

	public Collection<User> getUsers() {
		Collection<Long> userIds = getUserIds(0, Integer.MAX_VALUE);
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	public Collection<Long> getUserIds() {
		return getUserIds(0, Integer.MAX_VALUE);
	}

	private Collection<Long> getUserIds(int startIndex, int numResults) {
		List<Long> userIds = new ArrayList<Long>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(ALL_USERS);
				DbConnectionManager.setFetchSize(pstmt, 500);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					userIds.add(rs.getLong(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(
						con, ALL_USERS);
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex,
						numResults);
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					userIds.add(rs.getLong(1));
					count++;
				}
			}
			if (Log.isDebugEnabled()) {
				Log.debug("查询结果: " + userIds.size());
				LogResults(userIds);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return userIds;
	}

	public Collection<User> getUsers(int startIndex, int numResults) {
		Collection<Long> userIds = getUserIds(startIndex, numResults);
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	public Collection<User> findUsers(List<String> fields, List<String> querys) {
		return findUsers(fields, querys, 0, Integer.MAX_VALUE);
	}

	public Collection<User> findUsers(List<String> fields, List<String> querys,
			int startIndex, int numResults) {
		if (fields.isEmpty()) {
			return Collections.emptyList();
		}
		if (querys.isEmpty()) {
			return Collections.emptyList();
		}

		List<Long> userIds = new ArrayList<Long>(50);
		Connection con = null;
		PreparedStatement pstmt = null;
		int queries = 0;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder(90);
			sql.append("SELECT id FROM jawa_user WHERE ");
			boolean first = true;
			if (fields.contains("username")) {
				sql.append(" username LIKE ?");
				queries++;
				first = false;
			}
			if (fields.contains("name")) {
				if (!first) {
					sql.append(" AND");
				}
				sql.append(" name LIKE ?");
				queries++;
				first = false;
			}
			if (fields.contains("email")) {
				if (!first) {
					sql.append(" AND");
				}
				sql.append(" email LIKE ?");
				queries++;
			}
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(sql.toString());
				for (int i = 0; i < queries; i++) {
					pstmt.setString(i, querys.get(i));
				}
				rs = pstmt.executeQuery();
				while (rs.next()) {
					userIds.add(rs.getLong(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(
						con, sql.toString());
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex,
						numResults);
				for (int i = 0; i < queries; i++) {
					pstmt.setString(i, querys.get(i));
				}
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					userIds.add(rs.getLong(1));
					count++;
				}
			}
			if (Log.isDebugEnabled()) {
				Log.debug("查询结果: " + userIds.size());
				LogResults(userIds);
			}
		} catch (SQLException e) {
			Log.error("查询用户信息出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	/**
	 * 记录详细结果。
	 */
	private void LogResults(List<Long> listElements) {
		String callingMethod = Thread.currentThread().getStackTrace()[3]
				.getMethodName();
		StringBuilder sb = new StringBuilder(256);
		int count = 0;
		for (Long element : listElements) {
			if (count > 20) {
				Log.debug(callingMethod + " 结果: " + sb.toString());
				sb.delete(0, sb.length());
				count = 0;
			}
			sb.append(element).append(",");
			count++;
		}
		sb.append("。");
		Log.debug(callingMethod + " 结果: " + sb.toString());
	}

}
